"""
5.3.1 连接数据库
"""

import re
from flask import Flask, render_template, request
from flask_sqlalchemy import SQLAlchemy
import os


app = Flask(__name__)
# 通过URI连接数据库
# app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://root:1234567@localhost/flask_data'
app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///database.db"
# app.config["SQLALCHEMY_DATABASE_URI"] = (
#     "sqlite:///" + os.path.join(app.root_path, "database.db")
#     # 这里使用SQLite数据库，数据库文件会自动创建在根目录下
# )
# 动态追踪数据库的修改，不建议开启
app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False

db = SQLAlchemy(app)


"""
5.4.1 增加数据
"""
# https://sqlalchemy.flask.org.cn/en/3.1.x/quickstart/#check-the-sqlalchemy-documentation


@app.route("/init_db")
def init_db():
    # Import User inside the function to avoid circular import
    from model import User, IdentityCard, Department, Item

    # 创建User类的对象
    user1 = User(username="小明", email="123@qq.com", dept_id=1)
    user2 = User(username="小张", email="456@qq.com", dept_id=1)
    user3 = User(username="小红", email="789@qq.com", dept_id=2)
    id_card1 = IdentityCard(number="123456789012345678", person_id=1)
    id_card2 = IdentityCard(number="123456789012345679", person_id=2)
    id_card3 = IdentityCard(number="123456789012345670", person_id=3)
    dept1 = Department(name="Software")
    dept2 = Department(name="Network")
    item1 = Item(name="Computer")
    item2 = Item(name="Router")
    item3 = Item(name="Switch")
    user1.items.append(item1)
    user1.items.append(item2)
    user1.items.append(item3)
    user2.items.append(item1)
    user2.items.append(item2)
    # 将User类的对象添加到数据库会话中
    try:
        with db.session.begin():
            db.session.add(user1)
            db.session.add_all([user2, user3])
            db.session.add_all([id_card1, id_card2, id_card3])
            db.session.add_all([dept1, dept2])
        return "OK"
    except Exception as e:
        db.session.rollback()
        print(f"Error: {e}")
        return f"Error: {e}"
    # db.session.add_all([dept1, dept2])
    # db.session.add_all([item1, item2, item3])
    # db.session.add_all([user1, user2, user3])
    # db.session.commit()
    # # 使用commit()方法从会话提交至数据库
    # db.session.commit()


@app.route("/insert/<username>/<email>")
def insert_user(username, email):
    # Import User inside the function to avoid circular import
    from model import User

    # 创建User类的对象
    try:
        user = User(username=username, email=email)
        # 将User类的对象添加到数据库会话中
        db.session.add(user)
        # 使用commit()方法从会话提交至数据库
        db.session.commit()
    except Exception as e:
        db.session.rollback()
        return f"Error: {e}"
    return "OK"


"""
5.4.2	查询数据
"""


# # 定义路由及视图函数
@app.route("/users")
def users():
    from model import User

    # 查询第一条记录
    first_user = db.session.query(User).first()
    print(first_user)
    users = db.session.query(User).all()

    # 查询id >1 的记录
    users2 = db.session.query(User).filter(User.user_id > 1).all()
    print(type(users2))
    for u in users2:
        print(u)
    return "ok"
    # ---------分页 ------------
    # item_per_page = 5
    # page_number = request.args.get("page", 1, type=int)
    # users = db.session.query(User).paginate(page=page_number, per_page=item_per_page)
    # print(type(users))
    # return render_template("users.html", users=users)
    # 返回分页后的用户列表


# 查询用户
@app.route("/users/<user_id>")
def users_id(user_id):
    from model import User

    # 或者
    # 旧方法
    # user = db.session.query(User).get(user_id)
    user = db.session.get(User, user_id)
    user2 = db.session.query(User).filter(User.user_id == user_id).scalar()
    user3 = db.session.query(User).filter_by(user_id=user_id).first()
    # 有些地方可以写成这样，后面会遇到
    # user = User.query.get(user_id)
    if user2:
        print(user2)
        return f"User: {user2.username},find by id: {user2.user_id} "
        # return f"User: {user},find by id: {user.user_id},department: {user.departments.name} "
    else:
        return "User not found"


# 根据用户ID查询身份证号码
# 输出身份证号码及所属用户的用户名
@app.route("/id_number/<user_id>")
def id_number(user_id):
    from model import User, IdentityCard

    id_card = (
        db.session.query(IdentityCard)
        .join(User)
        .filter(User.user_id == user_id)
        .first()
    )
    print(id_card)
    print(type(id_card))
    if id_card:
        return f"ID Number: {id_card.number} ,owner: {id_card.user.username} "
    else:
        return "not found"


# 查询部门里面有多少人
@app.route("/dept/<dept_id>")
def dept_id(dept_id):
    from model import Department

    dept = db.session.query(Department).get(dept_id)
    print(type(dept.user))
    if dept:
        return f"Department: {dept.name}, has members: {', '.join([u.username for u in dept.user])} "
    else:
        return "Department not found"


# 谁买了电脑
@app.route("/items/<item_id>")
def items(item_id):
    from model import User, IdentityCard, Item, association_table

    item = (
        db.session.query(Item)
        .join(association_table)
        .filter(association_table.c.item_id == item_id)
        .all()
    )
    if item:
        for item in item:
            print(
                f"Item: {item.name} is bought by: {', '.join([u.username for u in item.user])}"
            )
        return f"Item: {item.name} is bought by: {', '.join([u.username for u in item.user])}"
    else:
        return "Item not found"


"""
5.4.3	更新数据
"""


@app.route("/update_name/<int:user_id>/<new_name>")
def update_user(user_id, new_name):
    # 返回主键值2对应的记录
    from model import User

    user = db.session.query(User).get(user_id)
    # 将username的值修改为"小兰"
    user.username = new_name
    db.session.commit()
    return "OK"


"""
5.4.4	删除数据
"""


@app.route("/delete/<int:user_id>")
def delete_user(user_id):
    # Import ser inside the function to avoid circular import
    from model import User

    user = db.session.query(User).get(user_id)
    db.session.delete(user)
    db.session.commit()
    return "OK"


@app.route("/delete_all")
def delete_all():
    # Import User inside the function to avoid circular import
    from model import User

    db.session.query(User).delete()
    db.session.commit()
    return "OK"


if __name__ == "__main__":
    app.run(debug=True, port=5001)
